package com.deng.performance.dao.impl;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.UUID;


import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.deng.common.dao.impl.OrganizationImpl;
import com.deng.common.vo.DispenseOrganizationVo;
import com.deng.common.vo.OrganizationVo;
import com.deng.maintenance.vo.RuleGroupVo;
import com.deng.maintenance.vo.RuleVo;
import com.deng.util.DateTools;
import com.deng.util.MsgReader;

import flex.messaging.MessageBroker;
import flex.messaging.messages.AsyncMessage;
import flex.messaging.util.UUIDUtils;

public class ReloadDetailThread extends Thread {

	private String stat_time_str;
	private int oId;
	
	public static void main(String s[]){
	}
	public void setOid(int oid)
	{
		this.oId = oid;
	}
	public void setYM(String ymd){
		stat_time_str = ymd;
		//month 要-1    year 要+1900
	}
	
	public String getOrganizationUrl(int oid)
	{
		try
		{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	Connection conn = DriverManager.getConnection(
        			new MsgReader().getPt("CAL_BONUSE_DB"),
        			new MsgReader().getPt("CAL_BONUSE_USER_NAME"),
        			new MsgReader().getPt("CAL_BONUSE_PW"));
			ResultSet rs = queryData(conn, "select * from t_sys_item_detail_url where organization_id = "+oid);
	        if(rs.next())
	        {
	        	return rs.getString("uid");
	        }
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return "";
	}
		
	public void reloadAllData(String ymd,int organization_id){
		try{
			System.out.print("科室："+organization_id);
			ReloadDetailThread t = new ReloadDetailThread();
			t.setOid(organization_id);
			t.setYM(ymd);
			t.start();
		}catch(Exception ex){
			ex.printStackTrace();
		}

	}
	protected int updateData(Connection conn,String sql){
		System.out.println("::::updateData:::::   "+sql);		
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);
			return pstmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();	
		}
		return 0;
	}
	protected ResultSet queryData(Connection conn,String sql){
		System.out.println("::::queryData:::::   "+sql);
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		}catch(Exception ex){
			ex.printStackTrace();	
		}
		return null;
	}
	
	@Override
	public void run() {
		try {
						   
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	Connection conn = DriverManager.getConnection(
        			new MsgReader().getPt("CAL_BONUSE_DB"),
        			new MsgReader().getPt("CAL_BONUSE_USER_NAME"),
        			new MsgReader().getPt("CAL_BONUSE_PW"));
        	List<OrganizationVo> ol = new ArrayList();
        	ResultSet rs;
        	if(oId == 0)
        	{
        		rs = queryData(conn, "select id,name,duty_id,(select name from DICT_DUTY_TYPE where DICT_DUTY_TYPE.id = duty_id) as duty_name from t_sys_stat_department where is_leaf = 1");
            	while(rs.next())
            	{
            		OrganizationVo vo = new OrganizationVo();
            		vo.id = rs.getInt("id");
            		vo.name = rs.getString("name");
            		vo.duty_id = rs.getInt("duty_id");
            		vo.duty_name = rs.getString("duty_name");
            		ol.add(vo);
            	}
        	}
        	else
        	{
        		rs = queryData(conn, "select id,name,duty_id,(select name from DICT_DUTY_TYPE where DICT_DUTY_TYPE.id = duty_id) as duty_name from t_sys_stat_department where id = "+oId);
            	while(rs.next())
            	{
            		OrganizationVo vo = new OrganizationVo();
            		vo.id = rs.getInt("id");
            		vo.name = rs.getString("name");
            		vo.duty_id = rs.getInt("duty_id");
            		vo.duty_name = rs.getString("duty_name");
            		ol.add(vo);
            	}
        	}
        	
        	for(OrganizationVo ovo:ol)
        	{
        		String sql = "select * from t_sys_item_detail_url where organization_id = "+ovo.id;
            	String filUrl = UUID.randomUUID().toString();
            	rs = queryData(conn,sql);
            	if(rs.next())
            	{
            		filUrl = rs.getString("uid");
            	}
            	else
            	{
            		updateData(conn, "insert into t_sys_item_detail_url" +
            				" (organization_id,uid)" +
            				" values("+ovo.id+",'"+filUrl+"')");
            	}
            	
        		rs = queryData(conn,"select g.organization_id,g.is_fee,g.name," +
    					"	g.group_id as gGid,u.group_id as uGid," +
    					"	g.is_mark as gMark,u.is_mark as uMark,u.fan as fan," +
    					"	u.unit_id,u.type_name,u.value,g.order_operate" +
    					"   from UNIT_PRICE_RULE_GROUP as g" +
    					"	left join UNIT_PRICE_RULE_UNIT as u" +
    					"	on g.organization_id = u.organization_id" +
    					"	and g.group_id = u.group_id" +
    					"	where g.organization_id = "+ovo.id+
    					"	order by gGid,unit_id");
    			
    			List<RuleGroupVo> l = new ArrayList<RuleGroupVo>();
    			String tmpGid="";
    			while(rs.next()){//生成规则组
    				String name = rs.getString("name");
    				String gGid = rs.getString("gGid");
    				String uGid = rs.getString("uGid");
    				int gMark = rs.getInt("gMark");
    				int uMark = rs.getInt("uMark");
    				int unit_id = rs.getInt("unit_id");
    				int order_operate = rs.getInt("order_operate");
    				int is_fee = rs.getInt("is_fee");
    				int fan = rs.getInt("fan");
    				String type_name = rs.getString("type_name");
    				String value = rs.getString("value");
    				
    				if(!tmpGid.equals(gGid))
    				{
    					tmpGid = gGid;
    					RuleGroupVo gVo = new RuleGroupVo();
    					gVo.name = name;
    					gVo.group_id = gGid;
    					gVo.is_mark = gMark;
    					gVo.order_operate = order_operate;
    					gVo.is_fee = is_fee;
    					List<RuleVo> l2 = new ArrayList<RuleVo>();
    					
    					
    					RuleVo vo = new RuleVo();
    					vo.is_mark = uMark;
    					vo.group_id = gGid;
    					vo.type_name = type_name;
    					vo.value = value;
    					vo.unit_id = unit_id;
    					vo.fan = fan;
    					l2.add(vo);
    					
    					gVo.ruleArr = l2;
    					l.add(gVo);
    				}
    				else
    				{
    					RuleVo vo = new RuleVo();
    					vo.is_mark = uMark;
    					vo.group_id = gGid;
    					vo.type_name = type_name;
    					vo.value = value;
    					vo.unit_id = unit_id;
    					vo.fan = fan;
    					for(int i = 0;i<l.size();i++)
    					{
    						RuleGroupVo tmpRg = l.get(i);
    						if(tmpRg.group_id.equals(gGid))
    						{
    							tmpRg.ruleArr.add(vo);
    							break;
    						}
    					}    					
    				}    				
    			}
    			
    			
    			
    			for(int i =0;i< l.size();i++){
    				StringBuffer sqlF = new StringBuffer();
    				RuleGroupVo gVo = l.get(i);
    				System.out.print(ovo.duty_name+"   "+ovo.name+"   "+gVo.name);
    				if(gVo.is_mark == 0){//大公式不是符号
    					if(gVo.is_fee == 1)//用费用统计
    					{
    						for(int j = 0;j < gVo.ruleArr.size();j++){
    							RuleVo vo = gVo.ruleArr.get(j);
    							if(j == 0){
    								sqlF.append(
    										"   select org_order_organization_id,org_order_organization_name," +
    										"	org_operate_organization_id,org_operate_organization_name," +
    										"	org_order_doctor_id,org_order_doctor_name," +
    										"	org_operate_doctor_id,org_operate_doctor_name," +
    										"	org_item_id,org_item_name," +
    										"	org_fee_type_name,org_fee_type_id," +
    										"	sum(org_item_count),sum(fee) from t_org_organization_item_count_month" +
    										"   where ");
    							}
    							
    							if(vo.is_mark == 1){//小公式不是符号
    								sqlF.append("	"+vo.value);
    							}else{
    								if(vo.fan == 1)//取反
    								{
    									if(vo.type_name.equals("org_order_organization_id")){									
    										sqlF.append("	org_order_organization_id not in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id not in (select original_id " +
    												"	from t_sys_stat_department_vs_original_department" +
    												"	where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								else
    								{
    									if(vo.type_name.equals("org_order_organization_id")){									
    										sqlF.append("	org_order_organization_id in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id in (select original_id " +
    												"	from t_sys_stat_department_vs_original_department" +
    												"	where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								
    							}
    							
    							
    							if(j == gVo.ruleArr.size()-1){
    								sqlF.append(
    									"	and stat_time  = '"+stat_time_str+"'" +
    									"	group by org_order_organization_id,org_order_organization_name," +
    									"	org_operate_organization_id,org_operate_organization_name," +
    									"	org_order_doctor_id,org_order_doctor_name," +
    									"	org_operate_doctor_id,org_operate_doctor_name," +
    									"	org_item_id,org_item_name," +
    									"	org_fee_type_name,org_fee_type_id");
    							}	
    						}
    					
    						rs = queryData(conn, sqlF.toString().replaceAll("@organization_id", ovo.id+""));
    						String excelName = ovo.duty_name+"-"+ovo.name+"  "+gVo.name;
    						String title[] = new String[]{"开单科室编码","开单科室名称","执行科室编码","执行科室名称",
    									"开单医生工号","开单医生姓名","执行医生工号","执行医生姓名",
    									"项目编码","项目名称","费用类别编码","费用类别名称","总数量","总价格"};
    						List<String[]> rtL = new ArrayList();
    						while(rs.next())
    						{
    							String value[] = new String[14];
    							value[0] = rs.getString(1);
    							value[1] = rs.getString(2);
    							value[2] = rs.getString(3);
    							value[3] = rs.getString(4);
    							value[4] = rs.getString(5);
    							value[5] = rs.getString(6);
    							value[6] = rs.getString(7);
    							value[7] = rs.getString(8);
    							value[8] = rs.getString(9);
    							value[9] = rs.getString(10);
    							value[10] = rs.getString(11);
    							value[11] = rs.getString(12);
    							value[12] = rs.getBigDecimal(13).toString();
    							value[13] = rs.getBigDecimal(14).toString();
    							rtL.add(value);
    						}
    						createExcel(filUrl,excelName, title, rtL);
    					}
    					else if(gVo.is_fee == 2)//数量
    					{
    						for(int j = 0;j < gVo.ruleArr.size();j++){
    							RuleVo vo = gVo.ruleArr.get(j);
    							if(j == 0){
    								sqlF.append(
    										"   select org_order_organization_id,org_order_organization_name," +
    										"	org_operate_organization_id,org_operate_organization_name," +
    										"	org_order_doctor_id,org_order_doctor_name," +
    										"	org_operate_doctor_id,org_operate_doctor_name," +
    										"	org_item_id,org_item_name," +
    										"	org_fee_type_name,org_fee_type_id," +
    										"	sum(org_item_count) from t_org_organization_item_count_month" +
    										"	where ");
    							}
    							
    							if(vo.is_mark == 1){//小公式不是符号
    								sqlF.append("	"+vo.value);
    							}else{
    								if(vo.fan == 1)
    								{
    									if(vo.type_name.equals("org_order_organization_id")){									
    										sqlF.append("	org_order_organization_id not in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id not in (select original_id " +
    												"       from t_sys_stat_department_vs_original_department" +
    												"       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								else
    								{
    									if(vo.type_name.equals("org_order_organization_id")){									
    										sqlF.append("	org_order_organization_id in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id in (select original_id " +
    												"       from t_sys_stat_department_vs_original_department" +
    												"       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								
    							}
    							
    							
    							if(j == gVo.ruleArr.size()-1){
    								sqlF.append(
    									"	and stat_time  = '"+stat_time_str+"'" +
    									"	group by org_order_organization_id,org_order_organization_name," +
    									"	org_operate_organization_id,org_operate_organization_name," +
    									"	org_order_doctor_id,org_order_doctor_name," +
    									"	org_operate_doctor_id,org_operate_doctor_name," +
    									"	org_item_id,org_item_name," +
    									"	org_fee_type_name,org_fee_type_id");
    							}	
    						}
    						rs = queryData(conn, sqlF.toString().replaceAll("@organization_id", ovo.id+""));
    						String excelName = ovo.duty_name+"-"+ovo.name+"  "+gVo.name;
    						String title[] = new String[]{"开单科室编码","开单科室名称","执行科室编码","执行科室名称",
    									"开单医生工号","开单医生姓名","执行医生工号","执行医生姓名",
    									"项目编码","项目名称","费用类别编码","费用类别名称","总数量"};
    						List<String[]> rtL = new ArrayList();
    						while(rs.next())
    						{
    							String value[] = new String[14];
    							value[0] = rs.getString(1);
    							value[1] = rs.getString(2);
    							value[2] = rs.getString(3);
    							value[3] = rs.getString(4);
    							value[4] = rs.getString(5);
    							value[5] = rs.getString(6);
    							value[6] = rs.getString(7);
    							value[7] = rs.getString(8);
    							value[8] = rs.getString(9);
    							value[9] = rs.getString(10);
    							value[10] = rs.getString(11);
    							value[11] = rs.getString(12);
    							value[12] = rs.getBigDecimal(13).toString();
//    							value[14] = rs.getBigDecimal("14").toString();
    							rtL.add(value);
    						}
    						createExcel(filUrl,excelName, title, rtL);
    					}
    					else if(gVo.order_operate == 1)//用项目的开单点数
    					{
    						for(int j = 0;j < gVo.ruleArr.size();j++){
    							RuleVo vo = gVo.ruleArr.get(j);
    							if(j == 0){
    								sqlF.append(
    										"   select org_order_organization_id,org_order_organization_name," +
    										"	org_operate_organization_id,org_operate_organization_name," +
    										"	org_order_doctor_id,org_order_doctor_name," +
    										"	org_operate_doctor_id,org_operate_doctor_name," +
    										"	org_item_id,org_item_name," +
    										"	org_fee_type_name,org_fee_type_id," +
    										"	a.count,UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION.order_point" +
    										"	from UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION,(" +
    										
    										"   select org_order_organization_id,org_order_organization_name," +
    										"	org_operate_organization_id,org_operate_organization_name," +
    										"	org_order_doctor_id,org_order_doctor_name," +
    										"	org_operate_doctor_id,org_operate_doctor_name," +
    										"	org_item_id,org_item_name," +
    										"	org_fee_type_name,org_fee_type_id," +
    										"	sum(org_item_count) as count" +
    										"	from t_org_organization_item_count_month" +
    										"	where ");
    							}
    							
    							if(vo.is_mark == 1){
    								sqlF.append("	"+vo.value);
    							}else{
    								if(vo.fan == 1)
    								{
    									if(vo.type_name.equals("org_order_organization_id")){
    										
    										sqlF.append("	org_order_organization_id not in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id not in (select original_id " +
    												"       from t_sys_stat_department_vs_original_department" +
    												"       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								else
    								{
    									if(vo.type_name.equals("org_order_organization_id")){
    										
    										sqlF.append("	org_order_organization_id in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id in (select original_id " +
    												"       from t_sys_stat_department_vs_original_department" +
    												"       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								
    							}
    							
    							
    							if(j == gVo.ruleArr.size()-1){
    								sqlF.append(
    									"   and stat_time = '"+stat_time_str+"'" +
    									"   group by org_order_organization_id,org_order_organization_name," +
    									"	org_operate_organization_id,org_operate_organization_name," +
    									"	org_order_doctor_id,org_order_doctor_name," +
    									"	org_operate_doctor_id,org_operate_doctor_name," +
    									"	org_item_id,org_item_name," +
    									"	org_fee_type_name,org_fee_type_id" +
    									"    ) as a" +
    									"	where a.org_item_id = UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION.item_id" +
    									"	and UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION.organization_id = "+ovo.id+
    									"	and order_point != 0");
    							}	
    						}
    						
    						rs = queryData(conn, sqlF.toString().replaceAll("@organization_id", ovo.id+""));
    						String excelName = ovo.duty_name+"-"+ovo.name+"  "+gVo.name;
    						String title[] = new String[]{"开单科室编码","开单科室名称","执行科室编码","执行科室名称",
    									"开单医生工号","开单医生姓名","执行医生工号","执行医生姓名",
    									"项目编码","项目名称","费用类别编码","费用类别名称","数量","开单点数"};
    						List<String[]> rtL = new ArrayList();
    						while(rs.next())
    						{
    							String value[] = new String[14];
    							value[0] = rs.getString(1);
    							value[1] = rs.getString(2);
    							value[2] = rs.getString(3);
    							value[3] = rs.getString(4);
    							value[4] = rs.getString(5);
    							value[5] = rs.getString(6);
    							value[6] = rs.getString(7);
    							value[7] = rs.getString(8);
    							value[8] = rs.getString(9);
    							value[9] = rs.getString(10);
    							value[10] = rs.getString(11);
    							value[11] = rs.getString(12);
    							value[12] = rs.getBigDecimal(13).toString();
    							value[13] = rs.getBigDecimal(14).toString();
    							rtL.add(value);
    						}
    						createExcel(filUrl,excelName, title, rtL);
    					}
    					else//用项目的执行点数
    					{
    						for(int j = 0;j < gVo.ruleArr.size();j++){
    							RuleVo vo = gVo.ruleArr.get(j);
    							if(j == 0){
    								sqlF.append(
    										"   select org_order_organization_id,org_order_organization_name," +
    										"	org_operate_organization_id,org_operate_organization_name," +
    										"	org_order_doctor_id,org_order_doctor_name," +
    										"	org_operate_doctor_id,org_operate_doctor_name," +
    										"	org_item_id,org_item_name," +
    										"	org_fee_type_name,org_fee_type_id," +
    										"	a.count,UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION.operate_point" +
    										"	from UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION,(" +
    										
    										"   select org_order_organization_id,org_order_organization_name," +
    										"	org_operate_organization_id,org_operate_organization_name," +
    										"	org_order_doctor_id,org_order_doctor_name," +
    										"	org_operate_doctor_id,org_operate_doctor_name," +
    										"	org_item_id,org_item_name," +
    										"	org_fee_type_name,org_fee_type_id," +
    										"	sum(org_item_count) as count" +
    										"	from t_org_organization_item_count_month" +
    										"	where ");
    							}
    							
    							if(vo.is_mark == 1){
    								sqlF.append("	"+vo.value);
    							}else{
    								if(vo.fan == 1)
    								{
    									if(vo.type_name.equals("org_order_organization_id")){
    										
    										sqlF.append("	org_order_organization_id not in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id not in (select original_id " +
    												"       from t_sys_stat_department_vs_original_department" +
    												"       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id not in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								else
    								{
    									if(vo.type_name.equals("org_order_organization_id")){
    										
    										sqlF.append("	org_order_organization_id in " +
    												"	(select original_id from t_sys_stat_department_vs_original_department where stat_id " +
    												"	in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_operate_organization_id")){
    										sqlF.append("	org_operate_organization_id in (select original_id " +
    												"       from t_sys_stat_department_vs_original_department" +
    												"       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
    									}else if(vo.type_name.equals("org_item_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_item_name")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_item_name in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_fee_type_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_fee_type_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("sjly")){
    										if(vo.value == null || vo.value.equals("")){
    											System.out.println("vo.value == null");
    										}else if(vo.value.equals("0")){
    											sqlF.append("	sjly=0");
    										}else if(vo.value.equals("1")){
    											sqlF.append("	sjly=1");
    										}
    									}else if(vo.type_name.equals("org_order_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_order_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}else if(vo.type_name.equals("org_operate_doctor_id")){
    										String[] strArr = vo.value.split(":");
    										sqlF.append("	org_operate_doctor_id in (");
    										for(int k =0;k< strArr.length ;k++){
    											sqlF.append("'"+strArr[k]+"',");
    										}
    										sqlF.delete(sqlF.length()-1, sqlF.length());
    										sqlF.append(")");
    									}
    								}
    								
    							}
    							
    							
    							if(j == gVo.ruleArr.size()-1){
    								sqlF.append(
    									"   and stat_time = '"+stat_time_str+"'" +
    									"   group by org_order_organization_id,org_order_organization_name," +
    									"	org_operate_organization_id,org_operate_organization_name," +
    									"	org_order_doctor_id,org_order_doctor_name," +
    									"	org_operate_doctor_id,org_operate_doctor_name," +
    									"	org_item_id,org_item_name," +
    									"	org_fee_type_name,org_fee_type_id" +
    									"    ) as a" +
    									"	where a.org_item_id = UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION.item_id" +
    									"	and UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION.organization_id = "+ovo.id+
    									"	and operate_point != 0");
    							}	
    						}
    						
    						rs = queryData(conn, sqlF.toString().replaceAll("@organization_id", ovo.id+""));
    						String excelName = ovo.duty_name+"-"+ovo.name+"  "+gVo.name;
    						String title[] = new String[]{"开单科室编码","开单科室名称","执行科室编码","执行科室名称",
    									"开单医生工号","开单医生姓名","执行医生工号","执行医生姓名",
    									"项目编码","项目名称","费用类别编码","费用类别名称","数量","执行点数"};
    						List<String[]> rtL = new ArrayList();
    						while(rs.next())
    						{
    							String value[] = new String[14];
    							value[0] = rs.getString(1);
    							value[1] = rs.getString(2);
    							value[2] = rs.getString(3);
    							value[3] = rs.getString(4);
    							value[4] = rs.getString(5);
    							value[5] = rs.getString(6);
    							value[6] = rs.getString(7);
    							value[7] = rs.getString(8);
    							value[8] = rs.getString(9);
    							value[9] = rs.getString(10);
    							value[10] = rs.getString(11);
    							value[11] = rs.getString(12);
    							value[12] = rs.getBigDecimal(13).toString();
    							value[13] = rs.getBigDecimal(14).toString();
    							rtL.add(value);
    						}
    						createExcel(filUrl,excelName, title, rtL);
    					}
    				}
    			}
    			System.out.println("------------------------------明细点数完成");
        	}
        	
        	MessageBroker msgBroker = MessageBroker.getMessageBroker(null);
			AsyncMessage msg = new AsyncMessage();
			msg.setDestination("pub-consumer");
			msg.setHeader("DSSubtopic", "finish-excel-detail");
			msg.setMessageId(UUIDUtils.createUUID());
			msg.setTimestamp(System.currentTimeMillis());
			msg.setBody(stat_time_str+"的明细已生成！是否打开目录？");
//			msg.setBody("http://localhost:8080/performance/ExcelDetail/[2012-1-1]护理-儿科监护  开单点数.xls");
			msgBroker.routeMessageToService(msg, null);
		}catch (Exception e) {
            e.printStackTrace();
        }
	}

	public void createExcel(String fileUrl,String excelName,String[] title,List<String[]> l)
	{
		try
		{
			String url = ReloadDetailThread.class.getResource("/").getPath()+"../.."+new MsgReader().getPt("EXCEL_DETAIL_URI")+"/"+fileUrl+"/";
			System.out.println(url);
			File f = new File(url);
			if(!f.exists())
			{
				f.mkdir();
			}
			excelName = "["+stat_time_str+"]"+excelName;
			WritableWorkbook wwb = Workbook.createWorkbook(new File(url+excelName+".xls"));
			WritableSheet ws = wwb.createSheet(excelName, 0);
			int columns = title.length;
			for(int i = 0;i<columns;i++)
			{
				Label lb = new Label(i,0,title[i]);
				ws.addCell(lb);				
			}
			for(int i = 0;i<l.size();i++)
			{
				String[] value = l.get(i);
				for(int j = 0;j < value.length ; j ++)
				{
					Label lb = new Label(j,i+1,value[j]);
					ws.addCell(lb);	
				}
			}
			wwb.write();
			wwb.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		
	}

}
